const mysql = require('mysql2/promise')

// 数据库配置
const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: 'GKL9512357',
  database: 'novel_platform',
  charset: 'utf8mb4'
}

// 模拟书架数据 - 模拟几个测试用户的书架
const mockBookshelfData = [
  // 用户1的书架 (admin用户)
  {
    userId: 1,
    bookshelfItems: [
      {
        novelId: 1, // 斗罗大陆
        status: 'reading',
        readingProgress: 75.5,
        isFavorite: true,
        lastReadChapterId: 1,
        addedAt: '2024-01-15 10:30:00',
        lastReadAt: '2024-01-20 15:45:00'
      },
      {
        novelId: 2, // 斗破苍穹
        status: 'reading',
        readingProgress: 45.2,
        isFavorite: false,
        lastReadChapterId: 2,
        addedAt: '2024-01-10 09:20:00',
        lastReadAt: '2024-01-19 20:15:00'
      },
      {
        novelId: 3, // 完美世界
        status: 'favorite',
        readingProgress: 20.8,
        isFavorite: true,
        lastReadChapterId: null,
        addedAt: '2024-01-12 14:30:00',
        lastReadAt: '2024-01-18 16:22:00'
      },
      {
        novelId: 4, // 都市之超级医圣
        status: 'finished',
        readingProgress: 100,
        isFavorite: false,
        lastReadChapterId: 3,
        addedAt: '2024-01-05 11:15:00',
        lastReadAt: '2024-01-17 22:30:00'
      }
    ]
  },
  // 用户2的书架
  {
    userId: 2,
    bookshelfItems: [
      {
        novelId: 1, // 斗罗大陆
        status: 'favorite',
        readingProgress: 88.3,
        isFavorite: true,
        lastReadChapterId: 1,
        addedAt: '2024-01-08 16:45:00',
        lastReadAt: '2024-01-20 12:30:00'
      },
      {
        novelId: 5, // 霸道总裁的小娇妻
        status: 'reading',
        readingProgress: 62.1,
        isFavorite: true,
        lastReadChapterId: 4,
        addedAt: '2024-01-14 13:20:00',
        lastReadAt: '2024-01-20 18:45:00'
      },
      {
        novelId: 7, // 大明锦衣卫
        status: 'reading',
        readingProgress: 35.7,
        isFavorite: false,
        lastReadChapterId: 5,
        addedAt: '2024-01-16 09:30:00',
        lastReadAt: '2024-01-19 21:15:00'
      }
    ]
  },
  // 用户3的书架
  {
    userId: 3,
    bookshelfItems: [
      {
        novelId: 2, // 斗破苍穹
        status: 'finished',
        readingProgress: 100,
        isFavorite: true,
        lastReadChapterId: 2,
        addedAt: '2024-01-03 10:15:00',
        lastReadAt: '2024-01-15 20:45:00'
      },
      {
        novelId: 3, // 完美世界
        status: 'reading',
        readingProgress: 56.8,
        isFavorite: false,
        lastReadChapterId: null,
        addedAt: '2024-01-11 15:30:00',
        lastReadAt: '2024-01-18 19:20:00'
      },
      {
        novelId: 6, // 星际争霸之虫族崛起
        status: 'favorite',
        readingProgress: 42.3,
        isFavorite: true,
        lastReadChapterId: null,
        addedAt: '2024-01-13 12:45:00',
        lastReadAt: '2024-01-17 14:30:00'
      }
    ]
  },
  // 用户4的书架
  {
    userId: 4,
    bookshelfItems: [
      {
        novelId: 1, // 斗罗大陆
        status: 'reading',
        readingProgress: 12.5,
        isFavorite: false,
        lastReadChapterId: 1,
        addedAt: '2024-01-18 08:30:00',
        lastReadAt: '2024-01-20 10:15:00'
      },
      {
        novelId: 4, // 都市之超级医圣
        status: 'favorite',
        readingProgress: 78.9,
        isFavorite: true,
        lastReadChapterId: 3,
        addedAt: '2024-01-09 17:20:00',
        lastReadAt: '2024-01-19 23:45:00'
      }
    ]
  }
]

// 模拟阅读历史数据
const mockReadingHistory = [
  // 用户1的阅读历史
  {
    userId: 1,
    novelId: 1,
    chapterId: 1,
    readingTime: 1800,
    createdAt: '2024-01-20 15:45:00'
  }, // 30分钟
  {
    userId: 1,
    novelId: 2,
    chapterId: 2,
    readingTime: 1200,
    createdAt: '2024-01-19 20:15:00'
  }, // 20分钟
  {
    userId: 1,
    novelId: 3,
    chapterId: 1,
    readingTime: 900,
    createdAt: '2024-01-18 16:22:00'
  }, // 15分钟

  // 用户2的阅读历史
  {
    userId: 2,
    novelId: 1,
    chapterId: 1,
    readingTime: 2100,
    createdAt: '2024-01-20 12:30:00'
  }, // 35分钟
  {
    userId: 2,
    novelId: 5,
    chapterId: 4,
    readingTime: 1500,
    createdAt: '2024-01-20 18:45:00'
  }, // 25分钟
  {
    userId: 2,
    novelId: 7,
    chapterId: 5,
    readingTime: 1350,
    createdAt: '2024-01-19 21:15:00'
  }, // 22.5分钟

  // 用户3的阅读历史
  {
    userId: 3,
    novelId: 2,
    chapterId: 2,
    readingTime: 1800,
    createdAt: '2024-01-15 20:45:00'
  }, // 30分钟
  {
    userId: 3,
    novelId: 3,
    chapterId: 1,
    readingTime: 1650,
    createdAt: '2024-01-18 19:20:00'
  }, // 27.5分钟
  {
    userId: 3,
    novelId: 6,
    chapterId: 1,
    readingTime: 1200,
    createdAt: '2024-01-17 14:30:00'
  }, // 20分钟

  // 用户4的阅读历史
  {
    userId: 4,
    novelId: 1,
    chapterId: 1,
    readingTime: 600,
    createdAt: '2024-01-20 10:15:00'
  }, // 10分钟
  {
    userId: 4,
    novelId: 4,
    chapterId: 3,
    readingTime: 2400,
    createdAt: '2024-01-19 23:45:00'
  } // 40分钟
]

async function migrateBookshelfData () {
  let connection

  try {
    console.log('🚀 开始迁移用户书架数据...')

    // 创建数据库连接
    connection = await mysql.createConnection(dbConfig)
    console.log('✅ 数据库连接成功')

    // 检查数据库中是否已有书架数据
    const [existingBookshelf] = await connection.execute(
      'SELECT COUNT(*) as count FROM user_bookshelf'
    )

    if (existingBookshelf[0].count > 0) {
      console.log(`⚠️  数据库中已存在 ${existingBookshelf[0].count} 条书架记录`)
      console.log(
        '如需重新迁移，请先清空表: DELETE FROM user_bookshelf; DELETE FROM reading_history;'
      )
      return
    }

    // 清空现有数据（如果有的话）
    await connection.execute('DELETE FROM reading_history')
    await connection.execute('DELETE FROM user_bookshelf')
    console.log('🧹 已清空现有书架和阅读历史数据')

    // 迁移书架数据
    console.log('📚 开始迁移书架数据...')
    let bookshelfCount = 0

    for (const userBookshelf of mockBookshelfData) {
      for (const item of userBookshelf.bookshelfItems) {
        try {
          // 插入书架记录
          await connection.execute(
            `
            INSERT INTO user_bookshelf 
            (user_id, novel_id, last_read_chapter_id, reading_progress, is_favorite, added_at, last_read_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
          `,
            [
              item.userId || userBookshelf.userId,
              item.novelId,
              item.lastReadChapterId,
              item.readingProgress,
              item.isFavorite ? 1 : 0,
              item.addedAt,
              item.lastReadAt
            ]
          )

          bookshelfCount++
          console.log(
            `  ✅ 用户${userBookshelf.userId} - 小说${item.novelId} - 进度${item.readingProgress}%`
          )
        } catch (error) {
          console.error(
            `  ❌ 插入书架数据失败 (用户${userBookshelf.userId}, 小说${item.novelId}):`,
            error.message
          )
        }
      }
    }

    console.log(`📚 书架数据迁移完成，共插入 ${bookshelfCount} 条记录`)

    // 迁移阅读历史数据
    console.log('📖 开始迁移阅读历史数据...')
    let historyCount = 0

    for (const historyItem of mockReadingHistory) {
      try {
        await connection.execute(
          `
          INSERT INTO reading_history 
          (user_id, novel_id, chapter_id, reading_time, created_at)
          VALUES (?, ?, ?, ?, ?)
        `,
          [
            historyItem.userId,
            historyItem.novelId,
            historyItem.chapterId,
            historyItem.readingTime,
            historyItem.createdAt
          ]
        )

        historyCount++
        console.log(
          `  ✅ 用户${historyItem.userId} - 小说${
            historyItem.novelId
          } - 阅读${Math.floor(historyItem.readingTime / 60)}分钟`
        )
      } catch (error) {
        console.error(`  ❌ 插入阅读历史失败:`, error.message)
      }
    }

    console.log(`📖 阅读历史迁移完成，共插入 ${historyCount} 条记录`)

    // 验证迁移结果
    console.log('\n📊 迁移结果统计:')

    const [bookshelfResult] = await connection.execute(`
      SELECT 
        COUNT(*) as total_bookshelf,
        COUNT(CASE WHEN is_favorite = 1 THEN 1 END) as favorite_count,
        AVG(reading_progress) as avg_progress
      FROM user_bookshelf
    `)

    const [historyResult] = await connection.execute(`
      SELECT 
        COUNT(*) as total_history,
        AVG(reading_time) as avg_reading_time
      FROM reading_history
    `)

    console.log(`  📚 书架记录: ${bookshelfResult[0].total_bookshelf} 条`)
    console.log(`  ⭐ 收藏数量: ${bookshelfResult[0].favorite_count} 条`)
    console.log(
      `  📊 平均进度: ${parseFloat(bookshelfResult[0].avg_progress).toFixed(
        1
      )}%`
    )
    console.log(`  📖 阅读历史: ${historyResult[0].total_history} 条`)
    console.log(
      `  ⏱️  平均阅读时长: ${Math.floor(
        historyResult[0].avg_reading_time / 60
      )} 分钟`
    )

    // 显示每个用户的书架统计
    console.log('\n👤 用户书架统计:')
    const [userStats] = await connection.execute(`
      SELECT 
        ub.user_id,
        u.username,
        COUNT(*) as book_count,
        COUNT(CASE WHEN ub.is_favorite = 1 THEN 1 END) as favorite_count,
        AVG(ub.reading_progress) as avg_progress
      FROM user_bookshelf ub
      LEFT JOIN users u ON ub.user_id = u.id
      GROUP BY ub.user_id, u.username
      ORDER BY ub.user_id
    `)

    userStats.forEach(stat => {
      console.log(
        `  👤 ${stat.username || `用户${stat.user_id}`}: ${
          stat.book_count
        }本书, ${stat.favorite_count}个收藏, 平均进度${parseFloat(
          stat.avg_progress
        ).toFixed(1)}%`
      )
    })

    console.log('\n🎉 用户书架数据迁移完成！')
    console.log('\n📝 接下来可以做的：')
    console.log('1. 启动服务器测试书架API：cd server && npm start')
    console.log('2. 登录前端查看个人书架功能')
    console.log('3. 运行检查脚本：node check-bookshelf-data.js')
  } catch (error) {
    console.error('❌ 迁移过程中出现错误：', error)
  } finally {
    if (connection) {
      await connection.end()
      console.log('🔐 数据库连接已关闭')
    }
  }
}

// 执行迁移
if (require.main === module) {
  migrateBookshelfData()
}

module.exports = { migrateBookshelfData }
